----------------------------------------------
-- @name: oracle_never_ignores_hints
-- @author: dion cho
-- @description: test for demonstrating that oracle never ignores hints
----------------------------------------------



-- create objects
drop table t1 purge;
drop table t2 purge;
drop table t3 purge;

create table t1(c1 int, c2 varchar2(10), c3 varchar2(10));
create table t2(c1 int, c2 int);
create table t3(c1 varchar2(10), c2 varchar2(10));

create index t1_n1 on t1(c1);
create index t1_n2 on t1(c2);
create index t2_n1 on t2(c1);

-- create correlated columns
insert into t1
select level, 'A', 'a'
from dual
connect by level <= 10000
;


insert into t1
select level+10000, 'B', 'b'
from dual
connect by level <= 1000
;

insert into t2
select level, level
from dual
connect by level <= 11000
;

insert into t3 values('A', 'a');
insert into t3 values('B', 'b');

commit;



@gather t1
@gather t2
@gather t3


-- index_ffs hint
explain plan for
select /*+ index_ffs(t1 t1(c1)) */
  c1
from t1
;

@plan


explain plan for
select /*+ index_ffs(t1 t1(c1)) */
  c1
from t1
where c1 is not null
;

@plan


-- ordered hint
explain plan for
select /*+ ordered */
  *
from
  t1, t2
where
  t1.c1 in (select c1 from t3 where c2 > :b1)
;

@plan

explain plan for
select /*+ ordered */
  *
from
  t1, t2
where
  t1.c1 in (select /*+ no_unnest */ c1 from t3 where c2 > :b1)
;

@plan


explain plan for
select /*+ leading(t1 t2) */
  *
from
  t1, t2
where
  t1.c1 in (select c1 from t3 where c2 > :b1)
;

@plan

explain plan for
select /*+ leading(t1 t3) */
  *
from
  t1, t2
where
  t1.c1 in (select c1 from t3 where c2 > :b1)
;

@plan


explain plan for
select /*+ leading(t3@subq t1 t2) */
  *
from
  t1, t2
where
  t1.c1 in (select /*+ qb_name(subq) */  c1 from t3 where c2 > :b1)
;

@plan_all


-- insert append
alter table t1 
add constraint pk_t1 primary key (c1)
using index
;

@dic t1_n1

-- see that append hint does not work
explain plan for
insert /*+ append */ into t1
select level+10000000, 'A', 'a'
from dual
connect by level <= 10000
;

@plan

alter table t1
drop constraint pk_t1;

drop index t1_n1;

alter table t1 
add constraint pk_t1 primary key (c1)
;

@dic pk_t1


-- but this works
explain plan for
insert /*+ append */ into t1
select level+10000000, 'A', 'a'
from dual
connect by level <= 10000
;

@plan



-- dynamic sampling
-- this works well
select 
  *
from t2,
    (select 
        c1, c2 
    from t1
    where t1.c2 = 'A' and
        t1.c3 = 'b'
    ) v
where t2.c1 = v.c1
;


explain plan for
select 
  *
from t2,
    (select 
        c1, c2 
    from t1
    where t1.c2 = 'A' and
        t1.c3 = 'b'
    ) v
where t2.c1 = v.c1
;

@plan


explain plan for
select 
  *
from t2,
    (select /*+ dynamic_sampling(t1 4) */
        c1, c2 
    from t1
    where t1.c2 = 'A' and
        t1.c3 = 'b'
    ) v
where t2.c1 = v.c1
;

@plan


-- this does not work
select
  *
from 
  t1
where
  c2 = 'A' and
  c3 = (select max(c2) from t3)
;


explain plan for
select
  *
from 
  t1
where
  c2 = 'A' and
  c3 = (select max(c2) from t3)
;

@plan


explain plan for
select /*+ dynamic_sampling(t1 4) */
  *
from 
  t1
where
  c2 = 'A' and
  c3 = (select max(c2) from t3)
;

@plan


-- this does not work
select 
  *
from
  t1, t2
where
  t1.c1 = t2.c1+100000
;


explain plan for
select 
  *
from
  t1, t2
where
  t1.c1 = t2.c1+100000
;

@plan


explain plan for
select /*+ dynamic_sampling(t1 4) dynamic_sampling(t2 4) */
  *
from
  t1, t2
where
  t1.c1 = t2.c1+100000
;

@plan
